package com.sds.image;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.sds.common.Common;

public class ImageDAO {

	/**
	 * @return DB connection
	 */
	private Connection connection() {
		Connection conn = null;
		try {
			Class.forName(Common.DB_CLASS_NAME);
			conn = DriverManager.getConnection(Common.DB_URL, Common.DB_ID,
					Common.DB_PW);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public String getNextSequence(){
		String ret = null;
		Connection conn = connection();

		if (conn == null) {
			return null;
		}
		String sql = "select LPAD(max(img_id)+1,6,'0') CURID from t_image";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs =pstmt.executeQuery();
			if(rs.next()){
				ret = rs.getString("CURID");				
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * @param RestaurantVO
	 * @return result code( 0 : success, minus : fail )
	 */
	public int insertImage(ImageVO image) {
		int ret = -1;
		Connection conn = connection();

		if (conn == null) {
			return ret;
		}
		String sql = "insert into t_image values(sequence_img_id.nextval, ?, ?, ?)";
		System.out.println(image);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, image.getImg_res_id());
			pstmt.setString(2, image.getImg_comment());
			pstmt.setString(3, image.getImg_path());
			ret = pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		}
		return ret;
	}

	public int deleteImage(int ID) {
		int ret = -1;
		Connection conn = connection();

		if (conn == null) {
			return ret;
		}
		String sql = "delete from t_image where img_id=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ID);
			ret = pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		}
		return ret;
	}

	/**
	 * @param res_ID
	 * @return
	 */
	public ArrayList<ImageVO> selectImageList(int res_ID) {
		ArrayList<ImageVO> imgList = new ArrayList<ImageVO>();
		Connection conn = connection();

		if (conn == null) {
			return null;
		}

		String sql = "select * from t_image where img_res_id=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, res_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				int img_id = rs.getInt("IMG_ID");
				int img_res_id = rs.getInt("IMG_RES_ID");
				String img_comment = rs.getString("IMG_COMMENT");
				String img_path = rs.getString("IMG_PATH");
				ImageVO image = new ImageVO(img_id, img_res_id, img_comment,
						img_path);
				imgList.add(image);
			}

			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return imgList;
	}

	public ArrayList<String> selectImageList(int[] res_ID) {
		ArrayList<String> img_path_list = new ArrayList<String>();
		Connection conn = connection();

		if (conn == null) {
			return null;
		}

		String sql = "select * from t_image where img_res_id=?";
		PreparedStatement pstmt = null;

		try {
			for (int i = 0; i < res_ID.length; i++) {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, res_ID[i]);
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					String img_path = rs.getString("IMG_PATH");
					img_path_list.add(img_path);
				} else {
					img_path_list.add("no_image.gif");
				}
			}

			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return img_path_list;
	}
}